package cn.hpclub.server.model;

import java.io.Serializable;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jfinal.ext.plugin.tablebind.TableBind;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Model;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;

/**
 * 实体类 - 订单（大掌柜app主题广场中除宽带预约以外的其他6种订单）
 * 
 */
@TableBind(tableName = "vgroup_zfbg")
public class VgroupZFBG extends Model<VgroupZFBG> implements Serializable{
    private static Logger          logger           = LoggerFactory.getLogger(VgroupZFBG.class);
    private static final long      serialVersionUID = 5108597504836776211L;
    public static final VgroupZFBG dao              = new VgroupZFBG();

    public String getSelectSql(){
        return "SELECT vz.phone_no, vz.business_code, vz.business_name, vz.user_id, vz.org_id, "
                + " (CASE vz.sale_type WHEN 0 THEN '未知' WHEN 1 THEN 'APP' WHEN 2 THEN '分享页面' ELSE '未知' END) as sale_type, "
                + " DATE_FORMAT( vz.create_time, '%Y-%m-%d %T' ) AS create_time, mo.user_name, mo.name, org.DisplayName, org.NumCode,"
                + " ( SELECT DisplayName FROM organization WHERE id = ( SELECT CityCode FROM organization WHERE vz.org_id = id )) AS city,"
                + " ( SELECT DisplayName FROM organization WHERE id = ( SELECT AreaCode FROM organization WHERE vz.org_id = id )) AS area,"
                + " ( SELECT class_name FROM organization_class WHERE enabled = '1' AND id = org.webSiteId ) AS sitetype, "
                + " IF ( orgType.orgIDTmp || orgType2.orgIDTmp2, '是', '否' ) AS is_accounts_manager ";
    }

    public String getExpectSql(ConcurrentHashMap<String, String> searchInfo){
        String sql = "FROM vgroup_zfbg vz LEFT JOIN member_org mo ON vz.user_id = mo.id"
                + " LEFT JOIN organization org ON vz.org_id = org.id"
                + " LEFT JOIN ( SELECT id AS orgIDTmp FROM `organization` WHERE ( parentid = 54470 OR parentid IN ( SELECT id FROM organization WHERE parentid = 54470 ))) AS orgType ON vz.org_id = orgType.orgIDTmp"
                + " LEFT JOIN ( SELECT org.id AS orgIDTmp2, orgcls.WebsiteId AS WebsiteIdType FROM organization org, organization_class orgcls WHERE org.WebsiteId = orgcls.id AND org.enabled = 1 AND orgcls.enabled = 1 AND ( orgcls.WebsiteId LIKE '6%' OR orgcls.WebsiteId = '1000' OR orgcls.WebsiteId = '4800' )) AS orgType2 ON vz.org_id = orgType2.orgIDTmp2";
        int orgId = InterfaceUtil.getOrgId();
        String sqlQuery = "";
        for(Entry<String, String> item : searchInfo.entrySet()){
            if(item.getKey().equals("org_id")){
                orgId = Integer.parseInt(item.getValue());
                continue;
            }
            sqlQuery += buildQuery(item);
        }
        sql += " WHERE vz.return_code=0 AND vz.org_id in("
                + InterfaceUtil.getChlidOrgIdSql(orgId, Constant.MAX_ORG_LEVEL, true) + ") " + sqlQuery
                + " ORDER BY create_time DESC";
        return sql;
    }

    private String buildQuery(Entry<String, String> item){
        String sql = "";
        if(item.getKey().equals("phonenumber") && StrKit.notBlank(item.getValue())){
            sql += " and (vz.phone_no like '%" + item.getValue() + "%') ";
        } else if(item.getKey().equals("startTime")){
            sql += " and vz.create_time >= '" + item.getValue() + " 00:00:00' ";
        } else if(item.getKey().equals("endTime")){
            sql += " and vz.create_time <= '" + item.getValue() + " 23:59:59' ";
        } else if(item.getKey().equals("business_name") && StrKit.notBlank(item.getValue())){
            sql += " and vz.business_name like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("business_code") && StrKit.notBlank(item.getValue())){
            sql += " and vz.business_code like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("NumCode") && StrKit.notBlank(item.getValue())){
            sql += " and org.NumCode = '" + item.getValue() + "' ";
        } else if(item.getKey().equals("user_name") && StrKit.notBlank(item.getValue())){
            sql += " and mo.name like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("user_phone") && StrKit.notBlank(item.getValue())){
            sql += " and mo.user_name like '%" + item.getValue() + "%' ";
        }
        return sql;
    }
}
